package jdbc;

/**
 * @Author admin
 * @Date 2019/4/26 8:15
 * 图书管理的工具类，用JDBC实现
 * 完成图书的增删改查操作
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 将book对象添加到数据库中，返回影响了几行
 * @param
 * @return
 * */
public class BookDB  {
    static Connection conn=JDBCUtils.getConnection();
    static PreparedStatement pstmt=null;
    static ResultSet rs = null;
    public int insert(Book book) {
        int c = 0;
        /*quert方法：根据图书名称查询图书对象，若不存在返回null*/
        Book book1 = query(book.getName());
        if (book1 != null) {
            return c;
        } else {
            /*将图书对象book添加到数据库*/
            /*获得数据库连接
             * 获取操作SQL语句的对象pstmt
             * 执行SQL语句
             * 关闭
             * */
            try {
                String sql = "insert into book(name,press,author,price) values(?,?,?,?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, book.getName());
                pstmt.setString(2, book.getPress());
                pstmt.setString(3, book.getAuthor());
                pstmt.setDouble(4, book.getPrice());

                c = pstmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return c;
        }
    }

    public void select1() {
        String sql="select * from book";
        try {
            pstmt = conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String press = rs.getString("name");
                String authpr = rs.getString("name");
                String price = rs.getString("name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    public Book query(String name){
        String sql="select * from book where name=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,name);
            rs=pstmt.executeQuery();
            /*因为图书名称不能重复，所以结果集中只查询出一条语句*/
            if (rs.next()){
                /*结果集中的内容 返回*/
                int id=rs.getInt("id");
                String bookName=rs.getString("name");
                String press=rs.getString("press");
                String author=rs.getString("author");
                double price=rs.getDouble("price");
                Book book = new Book(id,name,press,author,price);
                return book;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public void close(){
        JDBCUtils.close(conn,pstmt,rs);
    }

    public void query() {

    }
    public void select(){
        String sql = "select * from book";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String press = rs.getString("press");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                System.out.println(id+"\t"+name+"\t"+press+"\t"+author+"\t");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public ArrayList<Book> selectAll(){
       ArrayList<Book> arrayList = new ArrayList<Book>();
       String sql="select * from book";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String press = rs.getString("press");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                Book book = new Book(id,name,press,author,price);
                arrayList.add(book);
            }
            return arrayList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
